CREATE TABLE [dbo].[tb_SubserviciosAgentes]
(
[idSubServicio] [int] NOT NULL IDENTITY(1, 1),
[ServicioAgente] [int] NOT NULL,
[NroOrden9] [int] NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FechaBaja] [datetime] NULL,
[FechaTermino] [datetime] NULL,
[Movimiento] [int] NULL,
[TramiteAlta] [int] NULL,
[TramiteBaja] [int] NULL,
[NormaInicio] [int] NULL,
[NormaFin] [int] NULL,
[Plaza] [int] NULL,
[SituacionLaboral] [int] NOT NULL,
[SituacionExtraordinaria] [int] NULL,
[Nivel] [int] NULL,
[Modalidad] [int] NULL,
[Zona] [int] NULL,
[Licencia] [int] NULL,
[CodEnfermedad] [int] NULL,
[NroCertificado] [int] NULL,
[EntidadCertificadora] [int] NULL,
[FechaCertificado] [datetime] NULL,
[EstadoLiqExt] [int] NULL,
[NivelAnt] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[confirmada] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NormaAnterior] [int] NULL,
[NormaBajaAnterior] [int] NULL,
[NormaAltaAnterior] [int] NULL,
[NormaTemporal] [int] NULL,
[resalta] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[resbaja] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NormaTemporalBaja] [int] NULL,
[ObligacionesDiasDescontados] [int] NOT NULL CONSTRAINT [DF_tb_SubserviciosAgentes_ObligacionesDiasDescontados] DEFAULT ((0)),
[ObservacionesSARRHH] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[DuenosActuales] ON [dbo].[tb_SubserviciosAgentes]
FOR INSERT
AS

declare @idPlaza int
declare @SituacionRevista int
declare @Agente int

Select @idPlaza = Plaza from Inserted 
/*
Select @SituacionRevista = SituacionRevista, @Agente = Agente from tb_Serviciosagentes where idServicioagente = (Select ServicioAgente from inserted)

--Cuando se inserta un SS con movimiento = 1 se tiene que actualizar duenoactual o actualocupante
if (Select movimiento from inserted) = 1
begin
	if (@SituacionRevista = 1) or (@SituacionRevista = 2)
		update tb_Plazas set DuenoActual = @Agente, SitRevDuenoActual = @SituacionRevista where idPlaza = @idPlaza
	else
		update tb_Plazas set Ultimodocente = @Agente where idPlaza = @idPlaza
end
*/
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
	TRIGGER PARA DETERMINAR SI LOS PERIODOS DE FECHA DEL SUBSERVICIO SON VALIDOS
*/
CREATE TRIGGER [dbo].[PeriodoPuestoCorrecto] ON [dbo].[tb_SubserviciosAgentes] 
FOR INSERT
AS
SET NOCOUNT ON
/*
-- DATOS DE LA PLAZA QUE CUBRE EL SUBSERVICIO
DECLARE @Plaza int
 SELECT @Plaza = (SELECT Plaza from inserted)
DECLARE @FAltaP datetime
SELECT @FAltaP = (SELECT FechaAlta FROM tb_Plazas WHERE idPlaza = @Plaza)
DECLARE @FBajaP datetime
SELECT @FBajaP = (SELECT FechaBaja FROM tb_Plazas WHERE idPlaza = @Plaza)
DECLARE @baja varchar(1)
SELECT @baja = (SELECT baja FROM tb_Plazas WHERE idPlaza = @Plaza)

-- DATOS DE PERIODOS DE FECHA DEL SUBSERVICIO
DECLARE @FAltaSS datetime
SELECT @FAltaSS = (SELECT Fechaalta FROM inserted)
DECLARE @FBajaSS datetime
SELECT @FBajaSS = (SELECT FechaBaja FROM inserted)


IF (@FAltaSS < @FAltaP)
	RAISERROR(50008, 16, 1)
IF (@baja = 'S' and @FAltaSS > @FAltaP and @FBajaSS is null)
	RAISERROR(50008, 16, 1)
IF (@baja = 'S' and (@FBajaSS is not null) and @FAltaSS > @FAltaP and @FBajaSS > @FBajaP)
	RAISERROR(50008, 16, 1)

*/
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[SubServiciosSinBrechas] ON [dbo].[tb_SubserviciosAgentes]
FOR UPDATE
AS
SET NOCOUNT ON
/*
set dateformat dmy
declare @IFBaja datetime
declare @DFBaja datetime
declare @IFAlta datetime
declare @ServicioAgente int
declare @idSubServicio int
declare @Error int

Select @Error = 0


declare SubServicios cursor for Select I.Fechabaja, D.FechaBaja, i.FechaAlta, i.ServicioAgente, i.idSubServicio
from Inserted i, Deleted d where i.idSubServicio = d.idSubServicio

open SubServicios

fetch next from SubServicios into @IFBaja, @DFBaja, @IFAlta, @ServicioAgente, @idSubServicio

while @@fetch_status = 0
begin
	-- Si se modificaron las fechas de baja y no se trata del ultimo SS
	if @IFBaja <> @DFBaja and @IFAlta <> (Select max(FechaAlta) from tb_SubServiciosAgentes where ServicioAgente = @ServicioAgente)
	begin
		if not exists (Select * from tb_SubServiciosAgentes where ServicioAgente = @ServicioAgente and dateadd(day, -1, FechaAlta) = @IFBaja)
		begin
			-- print 'Entro al rollback de Trigger'
			rollback
		end
	end
	fetch next from SubServicios into @IFBaja, @DFBaja, @IFAlta, @ServicioAgente, @idSubServicio
end

close SubServicios
deallocate SubServicios





*/
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] ADD CONSTRAINT [PK_tb_SubServiciosAgentes] PRIMARY KEY CLUSTERED  ([idSubServicio]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idxlicencia] ON [dbo].[tb_SubserviciosAgentes] ([Licencia]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idxplaza] ON [dbo].[tb_SubserviciosAgentes] ([Plaza]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_ServicioAgente] ON [dbo].[tb_SubserviciosAgentes] ([ServicioAgente]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_ServicioAgenteFechaAlta] ON [dbo].[tb_SubserviciosAgentes] ([ServicioAgente], [FechaAlta] DESC) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Enfermedades] FOREIGN KEY ([CodEnfermedad]) REFERENCES [dbo].[tb_Enfermedades] ([idEnfermedad])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubserviciosAgentes_tb_EntidadesCertificadorasAptitud] FOREIGN KEY ([EntidadCertificadora]) REFERENCES [dbo].[tb_EntidadesCertificadorasAptitud] ([idEntidadAptitud])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_EstadoLiquidacionExterna] FOREIGN KEY ([EstadoLiqExt]) REFERENCES [dbo].[tb_EstadoLiquidacionExterna] ([idEstadoLiquidacion])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Licencias] FOREIGN KEY ([Licencia]) REFERENCES [dbo].[tb_Licencias] ([idLicencia])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubserviciosAgentes_tb_ModalidadesEnsenanza] FOREIGN KEY ([Modalidad]) REFERENCES [dbo].[tb_ModalidadesEnsenanza] ([idModalidad])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Movimientos] FOREIGN KEY ([Movimiento]) REFERENCES [dbo].[tb_MovimientosDocentes] ([idMovimiento])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_NivelesEnsenanza] FOREIGN KEY ([Nivel]) REFERENCES [dbo].[tb_NivelesEnsenanza] ([idNivelEnsenanza])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Normas1] FOREIGN KEY ([NormaFin]) REFERENCES [dbo].[tb_Normas] ([idNorma])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Normas] FOREIGN KEY ([NormaInicio]) REFERENCES [dbo].[tb_Normas] ([idNorma])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Plazas] FOREIGN KEY ([Plaza]) REFERENCES [dbo].[tb_Plazas] ([idPlaza])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_SituacionesExtraordinarias] FOREIGN KEY ([SituacionExtraordinaria]) REFERENCES [dbo].[tb_SituacionesExtraordinarias] ([idSituacionExtraordinaria])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_SituacionesLaborales] FOREIGN KEY ([SituacionLaboral]) REFERENCES [dbo].[tb_SituacionesLaborales] ([idSituacionLaboral])
GO
ALTER TABLE [dbo].[tb_SubserviciosAgentes] WITH NOCHECK ADD CONSTRAINT [FK_tb_SubServiciosAgentes_tb_Tramites] FOREIGN KEY ([TramiteAlta]) REFERENCES [dbo].[tb_Tramites] ([idTramite])
GO
